package controller;

import java.util.ArrayList;
import java.util.regex.Pattern;

import dto.ColumnDTO;
import dto.QueryParserDTO;

/**
 * Parsing query statement.
 * 
 * @author Stanley
 * 
 */
public class QueryParser {
	private String query;

	/**
	 * constructor with query
	 * 
	 * @param query
	 *            query statement
	 */
	public QueryParser(String query) {
		super();
		this.query = query;
	}

	/**
	 * Check query is create database statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isCreateDatabaseStatement() {
		String createDatabase = "\\s*(?i)create\\s+(?i)database\\s+\\w+\\s*";
		return this.query.matches(createDatabase);
	}

	/**
	 * Check query is create table statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isCreateTableStatement() {
		String createTableCols = "\\(\\s*(\\w+\\s+\\w+)(\\s*,\\s*(\\w+\\s+\\w+))*\\s*\\)";
		String createTable = "\\s*(?i)create\\s+(?i)table\\s+\\w+\\s*"
				+ createTableCols;
		return this.query.matches(createTable);
	}

	/**
	 * Check query is delete statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDeleteStatement() {
		String and = "(\\s+(?i)and\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*";
		String or = "(\\s+(?i)or\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*";
		String where = "(\\s+(?i)where\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*"
				+ and + or;
		String delete = "\\s*(?i)delete\\s+(?i)from\\s+\\w+\\s*" + where;

		return this.query.matches(delete);
	}

	/**
	 * Check query is drop database statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDropDatabaseStatement() {
		String dropDatabase = "\\s*(?i)drop\\s+(?i)database\\s+\\w+\\s*";

		return this.query.matches(dropDatabase);
	}

	/**
	 * Check query is drop table statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isDropTableStatement() {
		String dropTable = "\\s*(?i)drop\\s+(?i)table\\s+\\w+\\s*";

		return this.query.matches(dropTable);
	}

	/**
	 * Check query is insert statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isInsertStatement() {
		String insertCols = "(\\s+\\(\\s*(\\w+)(\\s*,\\s*\\w+)*\\s*\\))?";
		String insertValues = "\\(\\s*((\\'\\w+\\')|\\d+)(\\s*,\\s*((\\'\\w+\\')|\\d+))*\\s*\\)\\s*";
		String insert = "\\s*(?i)insert\\s+(?i)into\\s+(\\w+)" + insertCols
				+ "\\s+(?i)values\\s+" + insertValues + "\\s*";

		return this.query.matches(insert);
	}

	/**
	 * Check query is select statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isSelectStatement() {
		String selectCols = "(?i)(\\*|(\\w+)(\\s*,\\s*(\\w+))*)";

		String and = "(\\s+(?i)and\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*";
		String or = "(\\s+(?i)or\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*";
		String where = "((\\s+(?i)where\\s+((\\w+|\\d+)\\s*=\\s*(\\'\\w+\\'|\\d+)))*"
				+ and + or + ")*";

		String groupby = "(\\s+(?i)(group\\s+by\\s+)(\\w+)(\\s*,\\s*\\w+)*)*";

		// String having = "(\\s+(?i)having)*";

		String orderby = "(\\s+(?)order\\s+(?i)by\\s+(\\w+)(\\s*,\\s*\\w+)*)*";
		String select = "\\s*(?i)select\\s+" + selectCols
				+ "\\s+(?i)from\\s+\\w+" + where + groupby + orderby + "\\s*";

		return this.query.matches(select);
	}

	/**
	 * Check query is update statement or not.
	 * 
	 * @return true if matches and vice versa
	 */
	public boolean isUpdateStatement() {
		String updateData = "\\w+\\s*=\\s*(\\'\\w+\\'|\\d+)(\\s*,\\s*(\\w+\\s*=\\s*(\\'\\w+\\'|\\d+)))*\\s*";
		String update = "\\s*(?i)update\\s+\\w+\\s+(?i)set\\s+" + updateData;

		return this.query.matches(update);
	}

	/**
	 * parse query main method of QueryParser class
	 * 
	 * @return QueryParserDTO
	 * @throws Exception
	 */
	public QueryParserDTO parse() throws Exception {
		// create database
		if (isCreateDatabaseStatement()) {
			return parseCreateDatabaseQuery();
		}

		// create table
		if (isCreateTableStatement()) {
			return parseCreateTableQuery();
		}

		// delete
		if (isDeleteStatement()) {

		}

		// drop database
		if (isDropDatabaseStatement()) {
			return parseDropDatabaseQuery();
		}

		// drop table
		if (isDropTableStatement()) {
			return parseDropTableQuery();
		}

		// insert
		if (isInsertStatement()) {
			return parseInsertQuery();
		}

		// select
		if (isSelectStatement()) {
			return parseSelectQuery();
		}

		// update
		if (isUpdateStatement()) {

		}
		return null;
	}

	/**
	 * Parse select statement.
	 * 
	 * @return
	 */
	private QueryParserDTO parseSelectQuery() {
		Pattern p = Pattern.compile(
				"(select|from|where|order by|group by|having|join)",
				Pattern.CASE_INSENSITIVE);
		String[] strings = p.split(this.query);
		ArrayList<String> splits = removeAllEmptyString(strings);

		int selectType = splits.size();

		ArrayList<Object> data = new ArrayList<Object>();
		switch (selectType) {
		case 2: // select * from table
			// 1. Parse keys
			p = Pattern.compile("(,)");
			strings = p.split(splits.get(0));
			ArrayList<String> keys = removeAllEmptyString(strings);

			// 2. Parse table
			strings = p.split(splits.get(1));
			ArrayList<String> tables = removeAllEmptyString(strings);

			// 3. Add to data
			// Index 0 columns
			data.add(keys);
			// index 1 : tables
			data.add(tables);
		case 3:
			// 1. Parse keys
			p = Pattern.compile("(,)");
			strings = p.split(splits.get(0));
			keys = removeAllEmptyString(strings);

			// 2. Parse table
			strings = p.split(splits.get(1));
			tables = removeAllEmptyString(strings);

			// 3. Parse conditions
			strings = p.split(splits.get(2));
			ArrayList<String> conditions = removeAllEmptyString(strings);

			// 3. Add to data
			// Index 0 columns
			data.add(keys);
			// index 1 : tables
			data.add(tables);
			// index 2 : conditions
			data.add(conditions);
			return new QueryParserDTO(EQueryType.SELECT, data);
		default:
			break;
		}

		return new QueryParserDTO(EQueryType.SELECT, data);
	}

	/**
	 * Parse insert query
	 * 
	 * @return QueryParserDTO
	 * @author Stanley
	 * @throws Exception
	 */
	private QueryParserDTO parseInsertQuery() throws Exception {
		Pattern p = Pattern.compile("(insert|into|values|\\(|\\))",
				Pattern.CASE_INSENSITIVE);
		String[] strings = p.split(query);
		ArrayList<String> splits = removeAllEmptyString(strings);
		ArrayList<Object> data = new ArrayList<>();

		// If columns didn't exists in query
		if (splits.size() == 2) {
			// Index = 0 : table name
			data.add(splits.get(0).trim());
			// Index = 1 : values array
			data.add(parseInsertValues(splits.get(1).trim()));

		} else if (splits.size() == 3) {
			// Index = 0 : table name
			data.add(splits.get(0).trim());
			// Index = 1 : columns
			p = Pattern.compile(",");
			ArrayList<String> columns = removeAllEmptyString(p.split(splits
					.get(1).trim()));
			data.add(columns);
			// Index = 2 : values array
			ArrayList<String> values = parseInsertValues(splits.get(2).trim());
			data.add(values);
			if (columns.size() != values.size()) {
				throw new Exception("Insert statement is invalid");
			}
		}

		return new QueryParserDTO(EQueryType.INSERT, data);
	}

	/**
	 * Parse insert values from values string
	 * 
	 * @param valuesString
	 * @return
	 */
	private ArrayList<String> parseInsertValues(String valuesString) {
		Pattern p = Pattern.compile("(\\'|,)");
		String[] strings = p.split(valuesString);
		ArrayList<String> values = removeAllEmptyString(strings);

		return values;
	}

	/**
	 * Remove all null or empty string in string array.
	 * 
	 * @param strings
	 *            String[]
	 * @return ArrayList<String>
	 * @author Stanley
	 */
	private ArrayList<String> removeAllEmptyString(String[] strings) {
		ArrayList<String> splits = new ArrayList<String>();
		for (int i = 0; i < strings.length; i++) {
			if (!strings[i].trim().isEmpty()) {
				splits.add(strings[i].trim());
			}
		}
		return splits;
	}

	private QueryParserDTO parseDropTableQuery() {
		Pattern p = Pattern.compile("((table[^\\d+]))",
				Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.DROP_TABLE, data);
	}

	/**
	 * parse create database query
	 * 
	 * @return
	 */
	private QueryParserDTO parseCreateDatabaseQuery() {
		Pattern p = Pattern.compile("(database)", Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.CREATE_DATABSE, data);
	}

	/**
	 * parse create table query
	 * 
	 * @return
	 */
	private QueryParserDTO parseCreateTableQuery() {
		Pattern p = Pattern.compile("((table[^\\d+])|\\(|\\))",
				Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(this.query);

		String tblName = strings[1].trim();
		String cols = strings[2].trim();

		// split columns
		ArrayList<ColumnDTO> columnsDTO = new ArrayList<ColumnDTO>();
		p = Pattern.compile(",");
		String[] colList = p.split(cols);
		for (String string : colList) {
			if (string != null) {
				p = Pattern.compile("\\s+");
				String[] c = p.split(string.trim());
				columnsDTO.add(new ColumnDTO(c[0].trim(), c[1].trim()));
			}
		}

		ArrayList<Object> data = new ArrayList<>();
		// Add table name into data
		data.add(tblName);
		// Add columns
		data.add(columnsDTO);
		return new QueryParserDTO(EQueryType.CREATE_TABLE, data);
	}

	/**
	 * parse drop database query
	 * 
	 * @return
	 */
	private QueryParserDTO parseDropDatabaseQuery() {
		Pattern p = Pattern.compile("(database)", Pattern.CASE_INSENSITIVE);

		String[] strings = p.split(query);
		ArrayList<Object> data = new ArrayList<>(1);
		// Add database name into data
		data.add(strings[1].trim());
		return new QueryParserDTO(EQueryType.DROP_DATABASE, data);
	}

}
